SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[paChequearIncompatibilidadHoraria] 
-- <DESCRIPCION> Controla si un agente infringe la Incompatibilidad Horaria en una determinada Fecha..
-- <CONSIDERACIONES ESPECIALES> 
-- <TABLAS> tb_ServiciosAgentes, tb_SubServiciosAgentes, tb_Licencias, tb_ServiciosExternos, tb_CargosExternos, tb_Plazas.
-- <PROCEDIMIENTOS>
-- <ACTUALIZACIONES> 06 - 03 - 2006 por OTERO, Javier (se acomoda el control segun procedimiento de alta).
-- <ACTUALIZACIONES> 11 - 04 - 2006 por SANES, Jorge (cambios en los controles de incompatibilidad).
-- <ACTUALIZACIONES> 24 - 06 - 2006 por OTERO, Javier (se agrega parametro @Excluir para no tener en cuenta X servicio en el calculo).
-- <ACTUALIZACIONES> 26 - 12 -2007 por SANES, Jorge  (Cambio en Control de InCompatibilidad Horaria se cambio control de Fechas, cuando la Fecha de Alta y Baja del Servicio es igual no se tiene en cuenta OJO)
-- <ACTUALIZACIONES> 08 - 04 -2008 por SANES, Jorge  (Cambio en Control de InCompatibilidad Horaria se agrego control contra la Tb_RelIterservicio y las Plazas PT) (Referencia -- Agregado 08-04-2008 y ---  Quitado 08-04-2008)


---- ACTUALIZADO EN DB
-- 17/07/2006 - SANES, Jorge 


@Agente INT,
@HorasDeMediaIniciales INT,
@HorasTerciariasIniciales INT,
@HorasDeCargoIniciales INT,
@FechaDesde DATETIME,
@FechaHasta DATETIME,
@Incompatible BIT OUTPUT,
@Horas INT OUTPUT,
@TotalHorasTerciarias INT OUTPUT,
@Excluir INT = NULL
AS
SET NOCOUNT ON
SET DATEFORMAT dmy
-- Para almacenar la acumulacion horaria del agente en horas.
DECLARE @AcumulacionHorariaHoras INT
--Para almacenar la acumulacion horaria del agente en cargos (por equivalencia horaria del cargo)
DECLARE @AcumulacionHorariaCargo INT
-- Para almacenar la acumulacion horaria total.
DECLARE @AcumHoraria INT
-- Almacena el Total de Horas nuevas (de los movimientos que se pasan)
DECLARE @SumHorasNuevas INT
-- Horas dadas de alta para el Agente con Articulo 131
DECLARE @Horas131 INT
-- Total de horas terciarias que tiene el agente.
DECLARE @HorasTerciarias INT
-- Para almacenar el parametro TopeHorasDeServicios
DECLARE @TopeHorasDeServicios INT
-- Para almacenar el parametro TopeHorasTotalesDeServicios
DECLARE @TopeHorasTotales INT
-- Almacena las horas de Servicios Externos
DECLARE @HorasServiciosExternos INT
-- Almacena las horas totales
DECLARE @HorasTotales INT
-- Almacena la cantidad de Horas Terciarias en regimen salarial Cargos
DECLARE @AcumulacionHorariaCargoTerciaria INT
-- Almacena la cantidad de Horas Terciarias de Servicios Externos.
DECLARE @HorasServiciosExternosTeriarias INT

-- Inicializacion de Variables
SET @AcumulacionHorariaCargo = 0
SET @AcumulacionHorariaHoras = 0
SET @AcumHoraria = 0
SET @SumHorasNuevas = 0
SET @Horas131 = 0
SET @HorasTerciarias = 0
SET @TopeHorasDeServicios = 0
SET @TopeHorasTotales = 0
SET @HorasServiciosExternos = 0
SET @Incompatible = 0

-- Saca el Tope de Horas a controlar.
SELECT @TopeHorasDeServicios = CONVERT(INT, valor1) FROM tb_Parametros WHERE Parametro = 'TopeHorasDeServicios'
SELECT @TopeHorasTotales =  CONVERT(INT, valor1) FROM tb_Parametros WHERE Parametro = 'TopeHorasTotalesDeServicios'

-- Acumulacion de Horas
SELECT @AcumulacionHorariaHoras = ISNULL(SUM(S.Horas), 0)
FROM TB_ServiciosAgentes S
INNER JOIN TB_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente AND S.FechaAlta = SS.FechaAlta
INNER JOIN TB_Plazas P ON P.IdPlaza = SS.Plaza
WHERE EXISTS ( SELECT SSC.ServicioAgente FROM tb_SubserviciosAgentes SSC WHERE
				((@FechaDesde >= SSC.FechaAlta AND (SSC.FechaBaja IS NULL OR (SSC.FechaBaja IS NOT NULL AND SSC.FechaBaja >= @FechaDesde))) -- Se Cambio el 10-04-2006
				   	OR (@FechaDesde <= SSC.FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND SSC.fechaAlta <= @FechaHasta))))
				AND (ssc.licencia IS NULL OR ssc.licencia NOT IN (SELECT idLicencia FROM tb_Licencias WHERE cuentaincompatibilidad = 'N'))
				AND SSC.ServicioAgente = S.IdServicioAgente
			 )	
	AND S.regimensalarial = 13 
	AND S.TipodeServicio IN (1, 2) 
	AND S.IdServicioAgente NOT IN (SELECT  SRel.IdServicio FROM tb_RelInterservicios SRel) ---  Agregado 08-04-2008
	AND s.Agente = @Agente
	AND P.paquete IS NULL
	AND S.CargoSalarial <> 4001
	--AND S.CausaAlta NOT IN (12,18)  ---  Quitado 08-04-2008
	AND ((S.CausaAlta IN (9,19) AND P.Presupuesto<>4) OR (S.CausaAlta IN (SELECT CAS.idCausaAlta FROM Tb_CausasAltas CAS WHERE CAS.idCausaAlta NOT IN(9,12,18,19))))  ---  Agregado 08-04-2008
	AND ( (@Excluir IS NULL) OR (@Excluir IS NOT NULL AND S.IdServicioAgente <> @Excluir))
	AND ((S.FechaBaja IS NULL) OR (S.FechaBaja IS NOT NULL AND S.FechaAlta <> S.FechaBaja))

-- Horas de regimen salarial Cargo
SELECT @AcumulacionHorariaCargo = ISNULL(SUM(C.EquivHoraria),0)
FROM TB_ServiciosAgentes S
INNER JOIN TB_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente AND S.FechaAlta = SS.FechaAlta
INNER JOIN TB_CargosSalariales C ON C.IdCargo = S.CargoSalarial
INNER JOIN tb_Plazas P ON P.idPlaza = SS.Plaza  ---  Agregado 08-04-2008
WHERE EXISTS ( SELECT SSC.ServicioAgente FROM tb_SubserviciosAgentes SSC WHERE
				((@FechaDesde >= SSC.FechaAlta AND (SSC.FechaBaja IS NULL OR (SSC.FechaBaja IS NOT NULL AND SSC.FechaBaja >= @FechaDesde))) -- Se Cambio el 10-04-2006
				   	OR (@FechaDesde <= SSC.FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND SSC.fechaAlta <= @FechaHasta))))
				AND (ssc.licencia IS NULL OR ssc.licencia NOT IN (SELECT idLicencia FROM tb_Licencias WHERE cuentaincompatibilidad = 'N'))
				AND SSC.ServicioAgente = S.IdServicioAgente
			 )	
	AND S.RegimenSalarial = 12 
	AND S.TipodeServicio IN (1, 2) 
	AND S.IdServicioAgente NOT IN (SELECT  SRel.IdServicio FROM tb_RelInterservicios SRel) ---  Agregado 08-04-2008
	AND S.Agente = @Agente
	AND C.Escalafon<>24  --Linea Creada 3/4/2006
	--AND S.CausaAlta not in (12,18)  ---  Quitado 08-04-2008
	AND ((S.CausaAlta IN (9,19) AND P.Presupuesto<>4) OR (S.CausaAlta IN (SELECT CAS.idCausaAlta FROM Tb_CausasAltas CAS WHERE CAS.idCausaAlta NOT IN(9,12,18,19))))  ---  Agregado 08-04-2008
	AND ( (@Excluir IS NULL) OR (@Excluir IS NOT NULL AND S.IdServicioAgente <> @Excluir))
	AND ((S.FechaBaja IS NULL) OR (S.FechaBaja IS NOT NULL AND S.FechaAlta <> S.FechaBaja))

-- ACUMULACION CARGOS TERCIARIOS
SELECT @AcumulacionHorariaCargoTerciaria = ISNULL(SUM(C.EquivHoraria),0)
FROM TB_ServiciosAgentes S
INNER JOIN TB_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente AND S.FechaAlta = SS.FechaAlta
INNER JOIN TB_CargosSalariales C ON C.IdCargo = S.CargoSalarial
INNER JOIN tb_Plazas P ON P.idPlaza = SS.Plaza  ---  Agregado 08-04-2008
WHERE EXISTS ( SELECT SSC.ServicioAgente FROM tb_SubserviciosAgentes SSC WHERE
				((@FechaDesde >= SSC.FechaAlta AND (SSC.FechaBaja IS NULL OR (SSC.FechaBaja IS NOT NULL AND SSC.FechaBaja >= @FechaDesde))) -- Se Cambio el 10-04-2006
				   	OR (@FechaDesde <= SSC.FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND SSC.fechaAlta <= @FechaHasta))))
				AND (ssc.licencia IS NULL OR ssc.licencia NOT IN (SELECT idLicencia FROM tb_Licencias WHERE cuentaincompatibilidad = 'N'))
				AND SSC.ServicioAgente = S.IdServicioAgente
			 )	
	AND S.RegimenSalarial = 12 
	AND S.TipodeServicio IN (1, 2) 
	AND S.IdServicioAgente NOT IN (SELECT  SRel.IdServicio FROM tb_RelInterservicios SRel) ---  Agregado 08-04-2008
	AND S.Agente = @Agente
	AND C.Escalafon=24  
	--AND S.CausaAlta NOT IN (12,18) ---  Quitado 08-04-2008
	AND ((S.CausaAlta IN (9,19) AND P.Presupuesto<>4) OR (S.CausaAlta IN (SELECT CAS.idCausaAlta FROM Tb_CausasAltas CAS WHERE CAS.idCausaAlta NOT IN(9,12,18,19))))  ---  Agregado 08-04-2008
	AND ( (@Excluir IS NULL) OR (@Excluir IS NOT NULL AND S.IdServicioAgente <> @Excluir))
	AND ((S.FechaBaja IS NULL) OR (S.FechaBaja IS NOT NULL AND S.FechaAlta <> S.FechaBaja))

--Horas con Articulo 131
SELECT @Horas131 = ISNULL(SUM(S.Horas),0) 
FROM TB_ServiciosAgentes S 
INNER JOIN TB_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente AND S.FechaAlta = SS.FechaAlta
WHERE EXISTS ( SELECT SSC.ServicioAgente FROM tb_SubserviciosAgentes SSC WHERE
				((@FechaDesde >= SSC.FechaAlta AND (SSC.FechaBaja IS NULL OR (SSC.FechaBaja IS NOT NULL AND SSC.FechaBaja >= @FechaDesde))) -- Se Cambio el 10-04-2006
				   	OR (@FechaDesde <= SSC.FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND SSC.fechaAlta <= @FechaHasta))))
				AND SSC.ServicioAgente = S.IdServicioAgente
			 )	
	AND S.Agente = @Agente
	AND S.CausaAlta = 12
	AND S.IdServicioAgente NOT IN (SELECT  SRel.IdServicio FROM tb_RelInterservicios SRel) ---  Agregado 08-04-2008
	AND ( (@Excluir IS NULL) OR (@Excluir IS NOT NULL AND S.IdServicioAgente <> @Excluir))
	AND ((S.FechaBaja IS NULL) OR (S.FechaBaja IS NOT NULL AND S.FechaAlta <> S.FechaBaja))

--Horas Terciarias
SELECT @HorasTerciarias = ISNULL(SUM(S.Horas),0) 
FROM TB_ServiciosAgentes S
INNER JOIN TB_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente AND S.FechaAlta = SS.FechaAlta
INNER JOIN tb_Plazas P ON P.idPlaza = SS.Plaza  ---  Agregado 08-04-2008
WHERE EXISTS ( SELECT SSC.ServicioAgente FROM tb_SubserviciosAgentes SSC WHERE
				((@FechaDesde >= SSC.FechaAlta AND (SSC.FechaBaja IS NULL OR (SSC.FechaBaja IS NOT NULL AND SSC.FechaBaja >= @FechaDesde))) -- Se Cambio el 10-04-2006
				   	OR (@FechaDesde <= SSC.FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND SSC.fechaAlta <= @FechaHasta))))
				AND (ssc.licencia IS NULL OR ssc.licencia NOT IN (SELECT idLicencia FROM tb_Licencias WHERE cuentaincompatibilidad = 'N'))
				AND SSC.ServicioAgente = S.IdServicioAgente
			 )	
	AND S.CargoSalarial = 4001 
	AND S.TipodeServicio IN (1, 2) 
	AND S.IdServicioAgente NOT IN (SELECT  SRel.IdServicio FROM tb_RelInterservicios SRel) ---  Agregado 08-04-2008
	AND S.Agente=@Agente 
	--AND CausaAlta NOT IN (12,18)  ---  Quitado 08-04-2008
	AND ((S.CausaAlta IN (9,19) AND P.Presupuesto<>4) OR (S.CausaAlta IN (SELECT CAS.idCausaAlta FROM Tb_CausasAltas CAS WHERE CAS.idCausaAlta NOT IN(9,12,18,19))))  ---  Agregado 08-04-2008
	AND ( (@Excluir IS NULL) OR (@Excluir IS NOT NULL AND S.IdServicioAgente <> @Excluir))
	AND ((S.FechaBaja IS NULL) OR (S.FechaBaja IS NOT NULL AND S.FechaAlta <> S.FechaBaja))

--Horas MEDIAS de Servicios Externos  
SELECT @HorasServiciosExternos = ISNULL(SUM(ce.Horas),0) 
FROM TB_ServiciosExternos 
INNER JOIN TB_CargosExternos ce on  tb_serviciosexternos.cargoexterno=ce.id  
WHERE  idAgente=@Agente 
	AND ((@FechaDesde >= FechaAlta AND (FechaBaja IS NULL OR (FechaBaja IS NOT NULL AND FechaBaja >= @FechaDesde))) -- Se Cambio el 27-11-2007
		OR (@FechaDesde <= FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND FechaAlta <= @FechaHasta))))
	AND CE.seconsideransuperioresoterciarias='N' 

--Horas TERCIARIAS de Servicios Externos  
SELECT @HorasServiciosExternosTeriarias = ISNULL(SUM(ce.Horas),0) 
FROM tb_ServiciosExternos 
INNER JOIN tb_CargosExternos ce on  tb_serviciosexternos.cargoexterno=ce.id  
WHERE  idAgente=@Agente
	AND ((@FechaDesde >= FechaAlta AND (FechaBaja IS NULL OR (FechaBaja IS NOT NULL AND FechaBaja >= @FechaDesde))) -- Se Cambio el 27-11-2007
		OR (@FechaDesde <= FechaAlta AND (@FechaHasta IS NULL OR (@FechaHasta IS NOT NULL AND FechaAlta <= @FechaHasta))))
	AND ce.seconsideransuperioresoterciarias='S' 

-- Se sacan las horas totales terciarias y se incluyen las iniciales (que vienen por parametro del procedimiento)
SELECT @HorasTerciarias = @HorasTerciarias + @HorasServiciosExternosTeriarias + @AcumulacionHorariaCargoTerciaria + @HorasTerciariasIniciales
SELECT @AcumHoraria = @AcumulacionHorariaHoras + @AcumulacionHorariaCargo + @HorasServiciosExternos + @HorasDeCargoIniciales + @HorasDeMediaIniciales

SELECT @HorasTotales = @AcumHoraria

-- Realiza el control. 
IF (@HorasTotales + @HorasTerciarias) > @TopeHorasTotales
	SELECT @Incompatible = 1, @Horas = @HorasTotales, @TotalHorasTerciarias = @HorasTerciarias
ELSE
        IF (@HorasTotales) >@TopeHorasDeServicios
	SELECT @Incompatible = 1, @Horas = @HorasTotales, @TotalHorasTerciarias = @HorasTerciarias
GO
